const seq = require('./seq')
const bcryptjs = require('bcryptjs');
const { QueryTypes } = require('sequelize');
const moment = require('moment');
class User {
  //直接给用户赋权限，去掉中间角色
  async saveUserPerms(userid,menuselect,opselect){
    let sqldelin=''
    if(menuselect && menuselect.length>0)
    {
      sqldelin+=menuselect.map(item=>`${item.id}`).join(',')
    }
    if(sqldelin.trim()!==''){
      sqldelin+=','
    }
    if(opselect && opselect.length>0){
      sqldelin += opselect.map(item=>`${item.id}`).join(',')
    }
    if(sqldelin.trim()!==''){
      //sqlvalues=sqlvalues.substring(0, sqlvalues.length - 1)
      if(sqldelin.endsWith(',')) {
        sqldelin=sqldelin.substring(0, sqldelin.length - 1)
      } 
    }
    if(sqldelin.trim()!==''){
      let sqldel=` delete from userperms where userid =${userid}  and permOropid in(${sqldelin})`
      await seq.query(sqldel,{type:QueryTypes.DELETE})
    }

    let sql=`insert into userperms(userid,permOrOpid,permnOrOpame,flag) 
              values `
    let sqlvalues=''
    if(menuselect && menuselect.length>0){
      sqlvalues+=menuselect.map(item=>`(${userid},${item.id},'${item.permname}',1)`).join(',')
    }
    if(sqlvalues.trim()!==''){
      sqlvalues+=','
    }
    if(opselect && opselect.length>0){
      sqlvalues+=opselect.map(item=>`(${userid},${item.id},'${item.opname}',2)`).join(',')
    }
    if(sqlvalues.trim()!==''){
      //sqlvalues=sqlvalues.substring(0, sqlvalues.length - 1)
      if(sqlvalues.endsWith(',')) {
        sqlvalues=sqlvalues.substring(0, sqlvalues.length - 1)
      }
      sqlvalues+=';'
    }
    if(sqlvalues.trim()!==''){
      sql+=sqlvalues
      await seq.query(sql,{type:QueryTypes.INSERT})
    }
    
    //console.log('saveUserPerms:',sql)
    //return await seq.query(`
    // ${data.map(item=>`(${item.userid},${item.permsid})`).join(',')}`,{type:QueryTypes.INSERT})
  }

  async getUserPerms(userid) {
    const sql = `select * from userperms where userid =${userid}`
    console.log('getUserPerms:',sql)
    return await seq.query(sql,{type:QueryTypes.SELECT})
  }
  //直接给用户赋权限，去掉中间角色end
  async getAlluserRoles(userid) {
    console.log('getAlluserRoles:',userid)
    const sql = `select ur.*,u.username,r.name as rolename from userroles as ur
                left join  user as u on ur.userid=u.id
                left join  role as r on ur.roleid=r.id 
                where ur.userid=${userid}`
    console.log('getAlluserRoles:',sql)
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }) 
    return results
  }


  async saveUserRoles(data) {
    let {id,roles} = data
    const sqldel=`delete from userroles where userid=${id}` 
    await seq.query(sqldel,
      {
        type: QueryTypes.DELETE
      }
    )  
    //`INSERT INTO student (name, age, gender) VALUES 
    //('Alice', 18, 'female'), ('Bob', 20, 'male'), ('Charlie', 19, 'male')`
    if (roles && roles.length > 0){
      let sqlvalues=''
      for(let i=0;i<roles.length;i++)
      {
        if(i==roles.length -1 ){
          sqlvalues+= `(${id},${roles[i]})`
        }
        else{
          sqlvalues+= `(${id},${roles[i]}),`
        }
      }
      let sqlpermroles = `insert into userroles (userid,roleid) VALUES `
      sqlpermroles = sqlpermroles + sqlvalues
      await seq.query(sqlpermroles,
        {
          type: QueryTypes.INSERT
        }
      )
    }
  }

  async updateUserByID(User) {
    //缺少update时间
      //console.log('user',User);
      const{username,realName,mobile,deptID,
      formOfEmployment,timeOfEntry,avator,
      avatorOFileName,avatorNFileName,savepath,id} = User
      const currentTime = moment().format('YYYY-MM-DD HH:mm:ss');
      const dateentry = moment(timeOfEntry).format('YYYY-MM-DD')
      //console.log(currentTime);
      const sql = `update user set 
            username = ?,realName = ?,mobile = ?,deptID =?,
            formOfEmployment = ?,timeOfEntry = ?,
            avator=?,avatorOFileName=?,avatorNFileName=?,savepath=?,
            updatedtime=? where id=?`
      const results = await seq.query(sql,
            {
              replacements: [username,realName,mobile,deptID,formOfEmployment,dateentry,
                            avator,avatorOFileName,avatorNFileName,savepath,currentTime,id],
              type: QueryTypes.UPDATE
            })    
      return results
  }
  async delUserByID(id) {
    const currentTime = moment().format('YYYY-MM-DD');
    const sql = `update user set isDelete=1 ,updatedtime=? where id = ${id}`
    return await seq.query(sql, { 
      replacements: [currentTime] ,
      type: QueryTypes.UPDATE
      
    })
  }
  async adduser(data){
    //todo处理工号，处理deptName
    const{username,realName,mobile,deptID,
          formOfEmployment,timeOfEntry,avator='',
          avatorOFileName='',avatorNFileName='',savepath=''} = data
    const initPWD='123456'
    var salt = bcryptjs.genSaltSync(10);
    var hash = bcryptjs.hashSync(initPWD, salt);
    const currentTime = moment().format('YYYY-MM-DD');
    const sql = `insert into user set 
                username = ?,realName = ?,mobile = ?,deptID = ?,
                formOfEmployment = ?,timeOfEntry = ?,
                avator=?,avatorOFileName=?,avatorNFileName=?,savepath=?,updatedtime=?,password=? `
    const results = await seq.query(sql,
      {
        replacements: [username,realName,mobile,deptID,formOfEmployment,timeOfEntry,
                      avator,avatorOFileName,avatorNFileName,savepath,currentTime,hash],
        type: QueryTypes.INSERT
      }) 
    return results
  }

  async changePWD(id,oldpwd,newpwd){
    const sqlexist=`select * from user where id=${id}`
    const ret =await  seq.query(sqlexist, { 
      type: QueryTypes.SELECT
    })
  
    if(ret && ret.length>0)
    {
      const dbpwd=ret[0].password
      const pwdRight=bcryptjs.compareSync(oldpwd, dbpwd); 
      if(pwdRight)
      {
        //更新新密码
        const currentTime = moment().format('YYYY-MM-DD');
        const sql = `update user set password = ? ,updatedtime=? where id = ${id}`
        await seq.query(sql, { 
          replacements: [newpwd,currentTime] ,
          type: QueryTypes.UPDATE
        })
        return 1 //返回更新成功
      }
      else{
        return -1 //旧密码输入不对
      }
    }else{
      return -2 //用户不存在
    }
  }
  async login(username,password) {
    const sqlexist=`select * from user where username='${username}'`
    const results =await  seq.query(sqlexist, { 
      type: QueryTypes.SELECT
    })
    if(results && results.length>0)
    {
      const dbpwd=results[0].password
      const pwdRight=bcryptjs.compareSync(password, dbpwd); 
      if(!pwdRight)
      {
        return null
      }
    }
  if(results && results.length>0 && results[0].roles.indexOf('admin')===-1)
  {
    //菜单访问权限处理
    const longinuser = results[0];
    // const permsql=`select permname from permission where id in
    //               (
    //               select permid from permroles where roleid in(
    //               select ur.roleid  from user u,userroles ur
    //               where  u.id=ur.userid and  u.id=${longinuser.id})
    //               )`
    // const userperm = await seq.query(permsql,
    //   {
    //     type: QueryTypes.SELECT
    //   })
     // console.log("login:",userperm)
    let curuserroles='dashboard'
    // if(userperm && userperm.length>0)
    // {
    //   curuserroles =curuserroles+','+ userperm.map(item=>item.permname).join(',')
    // }
    ///直接使用人员权限，不实用角色
    const sqluserperm=`select * from userperms where userid =${longinuser.id}`
    const userperms=await seq.query(sqluserperm,{type: QueryTypes.SELECT})  
    if(userperms && userperms.length>0)
    {
      const usermenus=userperms.filter(item=>item.flag===1)
      longinuser.roles=curuserroles+','+usermenus.map(item=>`${item.permnOrOpame}`).join(',')
      const userops=userperms.filter(item=>item.flag===2)
      longinuser.oprations=userops.map(item=>`${item.permnOrOpame}`).join(',')
    }
    else{
      longinuser.roles=curuserroles
    }
    ///人员权限end
    //console.log("Roles:",curuserroles)
    //longinuser.roles=curuserroles
    //下面是按钮权限处理
    // const sqloprations=`select opname from oprations where isdelete=0 and id in(					
    //                     select opid from oproles where roleid in(
    //                     select ur.roleid  from user u,userroles ur
    //                     where  u.id=ur.userid and  u.id=${longinuser.id})
    //     )	`
    // const userop = await seq.query(sqloprations,
    //   {
    //     type: QueryTypes.SELECT
    //   })
    //   // console.log("login:",userperm)
    // let curuserops=''
    // if(userop && userop.length>0)
    // {
    //   curuserops = userop.map(item=>item.opname).join(',')
    // }
    // //console.log("Roles:",curuserroles)
    // longinuser.oprations=curuserops
  }
    return results
  }
  async getUserByID(id){
    const sql = `select * from user where id=${id}`
    console.log(id)
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      })
    return results
  }
  async getUsersBydeptid(id){
    
    const sql = `select * from user where deptID=${id}`
   // console.log(id)
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      })
    return results
  }
  
  //校验登录名称是否重复，登录名称存在username字段，真实姓名存realname字段
  async verifyUserNameeRepeat(id,username){
    const sql = `select * from user where username = ? and id<>${id}`
    const results = await seq.query(sql,{
      replacements:[username],
      type:QueryTypes.SELECT
    }
    )
    return results
  }
  //校验手机号是否重复
  async verifyMobileRepeat(id,mobile){
    const sql = `select * from user where mobile = ? and id<>${id}`
    const results =await  seq.query(sql,{
      replacements:[mobile],
      type:QueryTypes.SELECT
    }
    )
    return results
  }

  async updateAavtor(data)
  {
    const {id,avator,avatorOFileName,avatorNFileName} =data
    const sql = `update user set avator = ?,avatorOFileName = ?,avatorNFileName = ? where id = ?`
    const results = await seq.query(sql,
      {
        replacements: [avator,avatorOFileName,avatorNFileName,id],
        type: QueryTypes.UPDATE
      })
    return results
  }
  

  //id,为1：所有用户，否则为当前部门及子部门下的员工
  /**  根据起始条件ID，找出所有pid=id的子节点id, 返回集合里面也包含了始条件ID的值
select * from user where isdelete=0 and username like '%d%' and deptid in 
(WITH RECURSIVE cte AS (
SELECT id  FROM department WHERE id=1-- 设置起始条件为根节点
UNION ALL
SELECT t.id FROM department t INNER JOIN cte ON t.pid = cte.id -- 连接当前行与上一次递归得到的结果集合
)
SELECT * FROM cte)
   * @returns 
   */
  async getUserList(params) {
    const {selectedDeptId=1,pageNum=1,pageSize=10,searchKey} =params 
    const start = (pageNum-1)*pageSize
    const limit = pageSize 
    let sqlbegin=`select a.*,b.name as deptname  from ( ` 
    let sqlbasic='select * from user where isdelete=0 '  
    let sqlsearch=''
    if(searchKey)
    {
      sqlsearch=`and user.realName like '%${searchKey}%' `
    }         
    let sqldeptIDS=`(WITH RECURSIVE cte AS (
                    SELECT id  FROM department WHERE id=${selectedDeptId}
                    UNION ALL
                    SELECT t.id FROM department t INNER JOIN cte ON t.pid = cte.id 
                    )
                    SELECT * FROM cte)` 
    let sqlend=` )as a left join department as b on b.id=a.deptID `                
    let sqllimit=`limit ${start},${limit}`
    let sql = `${sqlbegin} ${sqlbasic} ${sqlsearch} and deptid in ${sqldeptIDS} ${sqlend}  ${sqllimit}`
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      })
    return results
  }

  async getUserListCount(params) {
    const {selectedDeptId=1,pageNum=1,pageSize=10,searchKey} =params 
    const start = (pageNum-1)*pageSize
    const limit = pageSize 
    let sqlbasic='select count(id) as total from user where isdelete=0 '  
    let sqlsearch=''
    if(searchKey)
    {
      sqlsearch=`and user.username like '%${searchKey}%' `
    }         
    let sqldeptIDS=`(WITH RECURSIVE cte AS (
                    SELECT id  FROM department WHERE id=${selectedDeptId}
                    UNION ALL
                    SELECT t.id FROM department t INNER JOIN cte ON t.pid = cte.id 
                    )
                    SELECT * FROM cte)` 
    let sqllimit=`limit ${start},${limit}`
    let sql = `${sqlbasic} ${sqlsearch} and deptid in ${sqldeptIDS} `
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      })
    return results
  }
}



module.exports = new User()